#include "bdd.h"
#include "qsqlfield.h"
#include <QMessageBox>

bdd::bdd()
{
    //db=NULL;
}


bdd::bdd(QString nomBdd)
{
    this->Connexion(nomBdd);
}

/********************************************************************************************
                                       METHODES
********************************************************************************************/

// Connexion et paramétrage de la base de données
void bdd::Connexion(QString nomBdd)
{
//    if(db == NULL)
//    {
        db = QSqlDatabase::database(nomBdd);
        if(!db.isValid())
        {
            db = QSqlDatabase::addDatabase( "QSQLITE",nomBdd );
            db.setDatabaseName(nomBdd);
            db.setConnectOptions("QSQLITE_OPEN_READONLY = 0");
        }
        if(!db.isOpen())
        {
            //Si l'ouverture à échoué
            if( !this->db.open() )
            {
                //On informe l'user
                QErrorMessage messageErreur;
                //Affichage du message d'erreur
                messageErreur.showMessage(db.lastError().text());
                messageErreur.exec();
            }
            //Connexion réussie
            else
            {
                // Activation des clés étrangères dans SQLITE
                this->Execute("PRAGMA foreign_keys = ON;");
            }
        }
//        db = QSqlDatabase::database(nomBdd);
//        if(!db.isValid())
//            QMessageBox::information(NULL,"marche pas","");
//    }
//    //Driver utilisé
//    this->db = QSqlDatabase::addDatabase( "QSQLITE" );

//    //Nom de la base de la donnée locale
//    this->db.setDatabaseName(nomBdd);

//    // On l'ouvre en lecture et écriture
//    this->db.setConnectOptions("QSQLITE_OPEN_READONLY = 0");

//    //Si l'ouverture à échoué
//    if( !this->db.open() )
//    {
//        //On informe l'user
//        QErrorMessage messageErreur;
//        //Affichage du message d'erreur
//        messageErreur.showMessage(db.lastError().text());
//        messageErreur.exec();
//    }
//    //Connexion réussie
//    else
//    {
//         // Activation des clés étrangères dans SQLITE
//         this->Execute("PRAGMA foreign_keys = ON;");
//    }
}

void bdd::Deconnexion()
{
    //this->db.close();
}

// Execute une requete SQL
void bdd::Execute(QString requete)
{
    this->requete.clear();

    this->requete = this->db.exec(requete);

    //Verifie si la requete à été exécuté correctement
    if ( this->requete.lastError().isValid())
    {
        //Erreur lors de l'éxecution
        QErrorMessage messageErreur;
        messageErreur.showMessage(this->requete.lastError().text());
        messageErreur.exec();
    }
}

// Crée les tables nécessaires au fonctionnement du logiciel
void bdd::ConstruireTables()
{
    QString tableFilm = "CREATE TABLE IF NOT EXISTS 'film' ('idFilm' INTEGER PRIMARY KEY ASC,"
                                                           "'codeAllocineFilm' INTEGER,"
                                                           "'titreFilm' varchar(150),"
                                                           "'titreOriginalFilm' varchar(150) NOT NULL,"
                                                           "'synopsisFilm' text,"
                                                           "'codeAllocineBandeAnnonce' INTEGER,"
                                                           "'typeFilm' varchar(40),"
                                                           "'dateRealisationFilm' varchar(4),"
                                                           "'noteUtilisateurAllocineFilm' float,"
                                                           "'notePresseFilm' float,"
                                                           "'maNoteFilm' float,"
                                                           "'maCritiqueFilm' text,"
                                                           "'cheminAfficheFilm' varchar(300))";

    QString tablePersonne = "CREATE TABLE IF NOT EXISTS 'personne' ('idPersonne' INTEGER PRIMARY KEY ASC,"
                                                                 "'codeAllocinePersonne' INTEGER,"
                                                                 "'sexePersonne' INTEGER,"
                                                                 "'nomPersonne' varchar(150) NOT NULL,"
                                                                 "'dateNaissancePersonne' varchar(10),"
                                                                 "'lieuNaissancePersonne' varchar(40),"
                                                                 "'biographiePersonne' varchar(500),"
                                                                 "'cheminPhoto' varchar(500))";

    QString tableJouer = "CREATE TABLE IF NOT EXISTS 'jouer' ('idJouer' INTEGER PRIMARY KEY ASC,"
                                                             "idFilm INTEGER NOT NULL,"
                                                             "idPersonne INTEGER NOT NULL,"
                                                             "FOREIGN KEY(idFilm) REFERENCES film(idFilm),"
                                                             "FOREIGN KEY(idPersonne) REFERENCES personne(idPersonne))";

    QString tableRealiser = "CREATE TABLE IF NOT EXISTS 'realiser' ('idRealiser' INTEGER PRIMARY KEY ASC,"
                                                             "idFilm INTEGER NOT NULL,"
                                                             "idPersonne INTEGER NOT NULL,"
                                                             "FOREIGN KEY(idFilm) REFERENCES film(idFilm),"
                                                             "FOREIGN KEY(idPersonne) REFERENCES personne(idPersonne))";

    QString tableGenre = "CREATE TABLE IF NOT EXISTS 'genre' ('idGenre' INTEGER PRIMARY KEY ASC,"
                                                             "codeAllocineGenre INTEGER NOT NULL,"
                                                             "nomGenre varchar(100) NOT NULL)";

    QString tableNationalite = "CREATE TABLE IF NOT EXISTS 'nationalite' ('idNationalite' INTEGER PRIMARY KEY ASC,"
                                                             "nomNationalite varchar(100) NOT NULL,"
                                                             "codeAllocineNationalite INTEGER NOT NULL)";

    QString tableFilmNationalite = "CREATE TABLE IF NOT EXISTS 'filmNationalite' ('idfilmNationalite' INTEGER PRIMARY KEY ASC,"
                                                                "idFilm INTEGER NOT NULL,"
                                                                "idNationalite INTEGER NOT NULL,"
                                                                "FOREIGN KEY(idFilm) REFERENCES film(idFilm),"
                                                                "FOREIGN KEY(idNationalite) REFERENCES nationalite(idNationalite))";

    QString tablePersonneNationalite = "CREATE TABLE IF NOT EXISTS 'personneNationalite' ('idAppartenirGenre' INTEGER PRIMARY KEY ASC,"
                                                                "idPersonne INTEGER NOT NULL,"
                                                                "idNationalite INTEGER NOT NULL,"
                                                                "FOREIGN KEY(idPersonne) REFERENCES personne(idPersonne),"
                                                                "FOREIGN KEY(idNationalite) REFERENCES nationalite(idNationalite))";

    QString tableAppartenirGenre = "CREATE TABLE IF NOT EXISTS 'appartenirGenre' ('idAppartenirGenre' INTEGER PRIMARY KEY ASC,"
                                                                "idFilm INTEGER NOT NULL,"
                                                                "idGenre INTEGER NOT NULL,"
                                                                "FOREIGN KEY(idFilm) REFERENCES film(idFilm),"
                                                                "FOREIGN KEY(idGenre) REFERENCES genre(idGenre))";

    QString tableDossier = "CREATE TABLE IF NOT EXISTS 'dossier' ('idDossier' INTEGER PRIMARY KEY ASC,"
                                                                "'cheminDossier' varchar(150) NOT NULL)";

    QString tableFichier = "CREATE TABLE IF NOT EXISTS 'fichier' ('idFichier' INTEGER PRIMARY KEY ASC,"
                                                                "'nomFichier' varchar(150) NOT NULL,"
                                                                "'langueFilm' varchar(10),"
                                                                "'cdFilm' INTEGER(2),"
                                                                "idFilm INTEGER,"
                                                                "idDossier INTEGER NOT NULL,"
                                                                "FOREIGN KEY(idFilm) REFERENCES film(idFilm),"
                                                                "FOREIGN KEY(idDossier) REFERENCES dossier(idDossier))";

    QString tableConfig = "CREATE TABLE IF NOT EXISTS 'config' ('idConfig' INTEGER PRIMARY KEY ASC,"
                                                                "'versionConfig' INTEGER NOT NULL,"
                                                                "'sousVersionConfig' INTEGER NOT NULL)";


    QStringList listeTables;
    listeTables << tableFilm << tablePersonne << tableJouer << tableRealiser
                << tableGenre << tableAppartenirGenre << tableDossier << tableFichier
                << tableNationalite << tableFilmNationalite << tablePersonneNationalite << tableConfig;

    // Execution des requêtes
    int i;
    for (i=0; i < listeTables.count(); i++)
    {
        this->Execute(listeTables.at(i));
    }
}

/********************************************************************************************
                                       METHODES D'UTILISATION
********************************************************************************************/

QString bdd::TexteVersSql(QString val)
{
    // Rajoute les '' autour d'une chaine de caractéres
    // échappe les caractéres
    QSqlField f;
    f.setType(QVariant::String);
    f.setValue(val);
    QString chaineRetour;

    return db.driver()->formatValue(f);
}

// Vérifie la présence d'un enregistrement dans une table
// : (passer la requête SELECT de l'enregistrement)
int bdd::EstDansLaBase(QString requete)
{
    this->Execute(requete);
    if (this->requete.next())
    {
        if (this->requete.next())
            return -2;
        else
            return this->requete.value(0).toInt();
    }
    // Aucun résultat
    else
        return -1;
}

/********************************************************************************************
                                       ACCESSEURS
********************************************************************************************/

QSqlQuery bdd::Requete()
{
    return this->requete;
}
